import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Iterator;
import java.util.LinkedList;


public class EventModel{

	/**
	 * Creates an event
	 * @param club_id  ID of hosting club
	 * @param name     Name of event
	 * @param location Location of event
	 * @return         True if successfully added, false if not
	 */
	public static boolean addEvent( int club_id, String name, String location, double cost ){
		String queries[] = {"INSERT INTO Event ( name, location ) VALUES ( '" + name + "', '" + location + "' )", 
				"INSERT INTO Holds_Event ( club_id, event_name, cost ) VALUES ( '" + club_id + "', '" + name + "', " + cost + " )" };
		return DB.Transaction( queries );
	}
	
	/**
	 * Deletes an event
	 * @param name Name of the event
	 * @return     True if successfully removed, false if not
	 */
	public static boolean removeEvent( String name ){
		String query_e = "DELETE FROM Event WHERE name='" + name +"'";
		String query_he = "DELETE FROM Holds_Event WHERE event_name='" + name +"'";
		String query_et = "DELETE FROM Event_Timings WHERE event_name='" + name +"'";
		return DB.Update( query_e ) && DB.Update( query_he ) && DB.Update( query_et );
	}
	
	/**
	 * Adds a time to an event
	 * @param event Event name
	 * @param start Start time
	 * @param end   End time
	 * @return      True if successfully added, false if not
	 */
	public static boolean addTime( String event, EventDate start, EventDate end ){
		// TIMESTAMP - format: YYYY-MM-DD HH:MM:SS
		String query = "INSERT INTO Event_Timings ( event_name, start_time, end_time ) VALUES ( '" + event + "', '" +
			start.year + "-" + start.month + "-" + start.day + " " + start.hour + ":" + start.minute + ":00', '" +
			end.year + "-" + end.month + "-" + end.day + " " + end.hour + ":" + end.minute + ":00' )";
		return DB.Update( query );
	}
	
	/**
	 * Removes a time from an event
	 * @param id Event timing id
	 * @return   True if successfully removed, false if not
	 */
	public static boolean removeTime( int id ){
		String query = "DELETE FROM Event_Timings WHERE id=" + id;
		return DB.Update( query );
	}
	
	/**
	 * Removes a time from an event
	 * @param name  Name of event
	 * @param start Start time of event
	 * @param end   End time of event
	 * @return      True if successfully removed, false if not
	 */
	public static boolean removeTime( String name, EventDate start, EventDate end ){
		// TIMESTAMP - format: YYYY-MM-DD HH:MM:SS
		String query = "DELETE FROM Event_Timings WHERE event_name='" + name + "' AND start_time='" +
			start.year + "-" + start.month + "-" + start.day + " " + start.hour + ":" + start.minute + ":00', AND end_time='" +
			end.year + "-" + end.month + "-" + end.day + " " + end.hour + ":" + end.minute + ":00'";
		return DB.Update( query );
	}
	
	/**
	 * Returns an array of events for the given club
	 * @param club_id Id of the club 
	 * @return        Array of clubs
	 */
	public static Event[] getEvents( int club_id ){
		String getEs = "SELECT name, location, cost FROM Event, Holds_Event WHERE name=event_name AND club_id=" + club_id + " ORDER BY name"; // Get Events
		String getTs = "SELECT id, event_name, start_time, end_time FROM Event_Timings WHERE"; // Get Timings
		ResultSet rs = DB.Query( getEs );
		
		// Get basic event info
		Event[] events;
		try{
			// Get number of rows, init array
			rs.last();
			events = new Event[rs.getRow()];
			rs.first();
			
			// Fill event info
			for( int i = 0; i < events.length; i++, rs.next() ){
				events[i] = new Event();
				events[i].cost = rs.getBigDecimal( "cost" );
				events[i].name = rs.getString( "name" );
				events[i].location = rs.getString( "location" );
				events[i].start = new Timestamp[0];
				events[i].end = new Timestamp[0];
				events[i].time_ids = new int[0];
				getTs += " event_name='" + events[i].name + "' OR";
			}
			
			// If no events, disable select timings statement
			if( events.length == 0 ){
				getTs += " 0";
			}else{
				getTs = getTs.substring( 0, getTs.length() - 3 ) + " ORDER BY event_name, start_time";
			}
			
		}catch( SQLException e ){
			// Query failure, return empty array
			System.err.println( e.getMessage() );
			return null;
		}
		
		// Get timings
		rs = DB.Query( getTs );
		try{
			String lastEvent = ""; // Track which event we're processing
			int index = -1;        // Track index in events array
			LinkedList<Timestamp> starts = new LinkedList<Timestamp>(); // Store start times
			LinkedList<Timestamp> ends = new LinkedList<Timestamp>();   // Store end times
			LinkedList<Integer> ids = new LinkedList<Integer>();        // Store timing ids
			while( rs.next() ){
				if( !rs.getString( "event_name" ).equals( lastEvent ) ){
					// A new event name has been found, save old info into the event and advance to next idnex
					if( index != -1 ){
						// Skip the first time, since index = -1
						events[index].start = starts.toArray( new Timestamp[0] );
						events[index].end = ends.toArray( new Timestamp[0] );
						events[index].time_ids = new int[ids.size()];
						Iterator<Integer> it = ids.iterator();
						int i = 0;
						while( it.hasNext() ){
							events[index].time_ids[i] = it.next();
							++i;
						}
						starts = new LinkedList<Timestamp>();
						ends = new LinkedList<Timestamp>();
						ids = new LinkedList<Integer>();
					}
					++index;
					lastEvent = rs.getString( "event_name" );
				}
				
				starts.add( rs.getTimestamp( "start_time" ) );
				ends.add( rs.getTimestamp( "end_time" ) );
				ids.add( rs.getInt( "id" ) );
			}
			
			if( index != -1 ){
				events[index].start = starts.toArray( new Timestamp[0] );
				events[index].end = ends.toArray( new Timestamp[0] );
				events[index].time_ids = new int[ids.size()];
				Iterator<Integer> it = ids.iterator();
				int i = 0;
				while( it.hasNext() ){
					events[index].time_ids[i] = it.next();
					++i;
				}
			}
		}catch( SQLException e ){
			// Query failure, return empty array
			System.err.println( e.getMessage() );
			return null;
		}
		
		return events;
	}
	

	
	public static void main( String args[] ){
		try{
			
			DB.Initialize();
//			EventDate s = new EventDate( "2010", "nov", "10", "12", "45" );
//			EventDate e = new EventDate( "2010", "nov", "10", "14", "50" );
//			EventDate s = new EventDate( "2010", "nov", "12", "10", "0" );
//			EventDate e = new EventDate( "2010", "nov", "12", "1", "30" );
//			System.out.println( addTime( "Romeo and Juliet", s, e ) );
//			System.out.println( removeTime(3) );
			
			Event[] es = getEvents(1);
			for( int i = 0; i < es.length; ++i ){
				System.out.println( es[i] );
			}
			
		}catch( DBError e1 ){ e1.printStackTrace(); }
	}
	
}
